﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

namespace AWS.SLAM.Hierarchy.Data
{
    internal class DataService
    {
        #region Private State

        private DataContext _dataContext;
        private static List<string> _verifiedTableList = new List<string>();
        private static bool _databaseCreated;
        private static string _dataSchema;

        #endregion

        #region Constructors and Destructors

        public DataService(string connectionString, string dataSchema)
        {
            _dataContext = new DataContext(connectionString);
            _dataSchema = dataSchema;
        }

        ~DataService()
        {
            _dataContext.Dispose();
        }

        #endregion

        #region Database Creation

        public void AddHierarchyColumns(string typeName)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.AddHierarchyColumns, _dataSchema, GetTableName(typeName)));
        }

        public void EnsureDatabaseExists()
        {
            if (!_databaseCreated && !_dataContext.DatabaseExists())
            {
                throw new DataException("Database does not exist.");
                //_dataContext.CreateDatabase();
            }

            _databaseCreated = true;
        }

        public bool VerifyHierarchyColumnsExist(string typeName)
        {
            EnsureDatabaseExists();
            string tableName = GetTableName(typeName);

            bool exists = false;
            if (_verifiedTableList.Contains(tableName))
            {
                exists = true;
            }
            else
            {
                exists = _dataContext.ExecuteQuery<int>(String.Format(Queries.VerifyHierarchyColumnsExist, _dataSchema, tableName)).SingleOrDefault() > 0;
                if (exists)
                {
                    _verifiedTableList.Add(tableName);
                }
            }
            return exists;
        }

        private string GetTableName(string typeName)
        {
            return Regex.Replace(typeName, "[\\W\\s]", "");
        }

        #endregion

        #region Data Management

        public void AddAsRootNode(string typeName, string listItemId)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.SetAsRootNode, _dataSchema, GetTableName(typeName), listItemId));
        }

        public void AddAsLastChildNode(string typeName, string parentListItemId, string listItemId)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.SetAsLastChildNode, _dataSchema, GetTableName(typeName), parentListItemId, listItemId));
        }

        public void AddAsLeftSiblingNode(string typeName, string siblingListItemId, string listItemId)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.SetAsLeftSiblingNode, _dataSchema, GetTableName(typeName), siblingListItemId, listItemId));
        }

        public int MoveToRootNode(string typeName, string listItemId)
        {
            return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToRootNode, _dataSchema, GetTableName(typeName), listItemId));
        }

        public int MoveToLastChildNode(string typeName, string parentListItemId, string listItemId)
        {
            return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToLastChildNode, _dataSchema, GetTableName(typeName), parentListItemId, listItemId));
        }

        public int MoveToLeftSiblingNode(string typeName, string siblingListItemId, string listItemId)
        {
            return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToLeftSiblingNode, _dataSchema, GetTableName(typeName), siblingListItemId, listItemId));
        }

        public void DeleteNode(string typeName, string listItemId)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.DeleteHierarchyBranch, _dataSchema, GetTableName(typeName), listItemId));
        }

        public void RecalculateHierarchyLevels(string typeName)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.RecalculateHierarchyLevels, _dataSchema, GetTableName(typeName)));
        }

        #endregion
    }
}
